# coding=utf-8

from flask import current_app,Blueprint,request,jsonify,make_response
from xlrd import open_workbook
import os
from utils.myf import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from utils.mydb import db1
from utils.xtree import xtree
from utils.xdata import xdata
from utils.getdata import getdata
from utils.myes import Es
import json
from utils.myredis import r
from utils.mytoken import token1

user_bp = Blueprint("user_bp",__name__)

@user_bp.route("/worklist")
def worklist():
    sql = "select * from workflow"
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

@user_bp.route("/getsource")
def getsource():
    values = r.str_get("datasource")
    if values:
        lst = eval(values)
    else:
        sql = "select * from data_source"
        res = db1.find_all(sql)
        lst = []
        if res:
            for i in res:
                dic = {"id":i["id"],"name":i["name"]}
                msql = "select id,name,type from %s"%i["tablename"]
                mres = db1.find_all(msql)
                dic["mes"] = mres
                lst.append(dic)
        r.str_set("datasource",json.dumps(lst))

    return jsonify({"code":200,"list":lst})



@user_bp.route('/addworkflow',methods=["POST"])
def addworkflow():
    data = getdata()
    sql = "select * from workflow where id=%d"%int(data["id"])
    res = db1.find(sql)
    if res:
        # print(json.loads(data["list"]))
        # data["list"] = json.loads(data["list"])
        # for i in data["list"]:
        #     i["value"]=json.loads(i["value"])
        # print(data["list"])
        usql = "update workflow set params='%s' where id=%d"%(str(data["list"]),int(res["id"]))
        db1.update(usql)
    return jsonify({"code":200,"msg":"修改成功"})

# 查询工作流
@user_bp.route("/query_workflow")
def query_workflow():
    sql = "select * from workflow"
    res = db1.find_all(sql)
    return jsonify({"code":200,"list":res})

# 数据去重
def data_distinct(self,result1):
    result = []
    dic = {}
    for i in result1:
        if i not in result:
            try:
                if dic[i]:
                    dic[i] += 1

            except:
                dic[i] = 1
    for k, v in dic.items():
        if v % 2 != 0:
            result.append(k)
        else:
            pass
    return result


# 添加任务到库中和redis
@user_bp.route('/addtask',methods=["POST"])
def addtask():
    data = getdata()
    title = str(json.loads(data["params"])["姓名"]+data["title"])
    sql = "insert into task(title,params,status,user_id,approver,next_approver) values('%s','%s',0,%d,'%s',%d)"%(title,data["params"],int(data["userid"]),data["approver"],int(json.loads(data["approver"])[0]))
    db1.update(sql)
    # qsql = "select name from users where id=%d"%int(json.loads(data["approver"])[0])
    # qres = db1.find(qsql)
    # qres["name"]
    qsql = "select * from task where title='%s'"%title
    qres = db1.find(qsql)
    key = "taskapprover" + str(json.loads(data["approver"])[0])
    r.hset_key(key,int(qres["id"]),title)
    return jsonify({"code":200,"msg":"添加成功"})


@user_bp.route("/menu_auth")
def menu_auth():
    userid = request.args.get("userid")
    sql = "select r.id,r.name,r.url,pr.name as pname,pr.id as pid from user_resource as ur left join resources as r on ur.resource_id=r.id left join resources as pr on r.pid=pr.id where user_id=%d and pr.type=1"%int(userid)
    res = db1.find_all(sql)
    list = xdata(res)
    return jsonify({"code":200,"list":list})


# 重构数据选择审批人
def xapprover(data):
    # data = [{"id":1,"name":"联通","pid":0},{"id":2,"name":"研发部","pid":1},{"id":3,"name":"研发yi部","pid":2}]
    tree = {}
    list = []
    for i in data:
        tree[i['id']] = i
        # tree[i['id']]["label"]=i["name"]
    for j in data:
        pid = j['pid']
        if not pid:
            list.append(j)
        else:
            if 'children' not in tree[pid]:
                tree[pid]['children'] = []
            # 查询此部门下所有员工
            sql = "select id as rid,name from users where dept_id=%d" % (j['id'])
            res = db1.find_all(sql)
            userlist = []
            if res:
                userlist = res
            j['children'] = userlist
            tree[pid]['children'].append(j)

    return list

@user_bp.route("/selectapprover")
def selectapprover():
    sql = "select * from company"
    res = db1.find_all(sql)
    list = xapprover(res)
    return jsonify({"code":200,"list":list})

@user_bp.route('/mytask')
def mytask():
    id = request.args.get("userid")
    sql = "select * from users where id=%d"%int(id)
    res = db1.find(sql)
    print(res)
    if res:
        lst = []
        key = "taskapprover"+str(res["id"])
        list = r.hget_keys(key)
        for k,v in list.items():
            lst.append({"id":str(k,encoding="utf8"),"title":str(v,encoding="utf8")})
        return jsonify({"code":200,"list":lst})
    return jsonify({"code":403,"msg":"id篡改"})

@user_bp.route('/taskquery')
def taskquery():
    taskid = request.args.get("taskid")
    sql = "select * from task where id='%s'"%int(taskid)
    res = db1.find(sql)
    return jsonify({"code":200,"list":res})


@user_bp.route('/taskrecordM', methods=['POST'])
def taskrecordM():
    data = getdata()
    # 写审批记录表
    time = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M:%S')
    try:
        sql = "insert into audit_record(taskid,userid,username,is_agree,reason,add_time) values(%d,%d,'%s',%d,'%s','%s')" % (
        int(data['taskid']), int(data['userid']), data['username'], int(data['agree']), data['reason'], time)
        db1.update(sql)
    except:
        return jsonify({"code": 10010, 'mes': '审批失败'})

    # 判断是否同意
    tsql = "select * from task where id=%d" % (int(data['taskid']))
    task = db1.find(tsql)
    key = 'taskapprover%d' % (int(data['userid']))
    # 清除当前redis中的信息
    r.hdel(key, int(data['taskid']))
    auditlist = json.loads(task['approver'])
    print(auditlist,type(auditlist))
    # 是不是最后一个审批人
    index = auditlist.index(int(data['userid']))
    print(index)
    if data['agree'] == '1':
        try:
            # 同意，查询任务，更新下一审批人，redis清除当前审批人，加入下一审批人，
            # 任务表中更新任务状态，如果是最后一个审批人，查新成已经完成，其它审核中
            if index == len(auditlist) - 1:
                sql1 = "update task set status=3,next_approver=0 where id=%d" % (int(data['taskid']))
                db1.update(sql1)
            else:
                sql1 = "update task set status=2,next_approver=%d where id=%d" % (auditlist[index + 1], int(data['taskid']))
                db1.update(sql1)
                # 添加redis
                newkey = 'taskapprover%d' % (int(auditlist[index + 1]))
                # 把下一审批人加入到redis
                r.hset_key(newkey, int(data['taskid']), task['title'])
        except:
            return jsonify({"code": 10020, 'mes': '失败'})
    else:
        # 不同意，把redis中当前审批人清除，任务表中的下一审批人清空
        if index == 0:
            sql1 = "update task set status=1,next_approver=0 where id=%d" % (int(data['taskid']))
            db1.update(sql1)
        else:
            sql1 = "update task set next_approver=%d where id=%d" % (int(auditlist[index - 1]), int(data['taskid']))
            db1.update(sql1)
            # 添加redis
            newkey = 'taskapprover%d' % (int(auditlist[index - 1]))
            # 把下一审批人加入到redis
            r.hset_key(newkey, int(data['taskid']), task['title'])
    return jsonify({"code": 200})

@user_bp.route('/mymes')
def mymes():
    userid = request.args.get("userid")
    sql = "select * from task where user_id=%d"%int(userid)
    res= db1.find_all(sql)
    lst = []
    for i in res:
        sql1 = "select * from audit_record where taskid=%d"%int(i["id"])
        res1 = db1.find_all(sql1)
        if res1:
            lst.append({"tid":str(i["id"]),"tname":str(i["title"]),"list":res1})
    noticesql = "select * from notice where type=3"
    nres = db1.find_all(noticesql)
    return jsonify({"code":200,"list":lst,"noticelist":nres})

import random

@user_bp.route('/Announcement',methods=["POST"])
def Announcement():
    data = getdata()
    sql = "insert into notice(title,content,status,type,stime,etime,level,notice_type) values('%s','%s',%d,%d,'%s','%s','%s','%s')"%(data["title"],data["content"],1,int(data["type"]),data["date"],data["edate"],data["level"],data["noticetype"])
    db1.update(sql)
    qsql = "select * from notice where title = '%s' and etime = '%s'"%(data["title"],data["edate"])
    qres = db1.find(qsql)

    if qres["type"]=="1":
        pass
    if qres["type"] == "2":
        pass
    if qres["type"] == "3":
        key = "allusernotice"+qres["id"]
        r.hset_key(key,qres["id"],qres["title"])
    return jsonify({"code":200,"msg":"添加成功"})